package com.food.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


import com.food.dao.in.CalorieDAO;
import com.food.model.Calorie;

public class CalorieDAOJdbc implements CalorieDAO {

//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	
	private DataSource ds = null;
	public CalorieDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	
	private static final String SELECT_BY_calcategory = "select calfood from calorie where calcategory=?";
	
	@Override
	public List<Calorie> findCategory(String calcategory) {
		
		
		List<Calorie> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
			
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_calcategory);
			stmt.setString(1, calcategory);
			rset = stmt.executeQuery();
			
			result = new ArrayList<Calorie>();
			while (rset.next()) {
				Calorie temp = new Calorie();
				
				temp.setCalfood(rset.getString("calfood"));
				
				
				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT_BY_calunit = "select calunit,calcalorie from calorie where calfood = ?";
	
	@Override
	public Calorie findFood(String calfood) {
		Calorie result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_calunit);
			stmt.setString(1, calfood);
			rset = stmt.executeQuery();
			if(rset.next()) {
				result = new Calorie();
				result.setCalunit(rset.getString("calunit"));
				result.setCalcalorie(rset.getDouble("calcalorie"));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset!=null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	public static void main(String[] args) {

//		CalorieDAO dao = new CalorieDAOJdbc();

		//System.out.println(bean);



	}

}
